from flask import current_app,Blueprint,request,jsonify,make_response
from xlrd import open_workbook
import os
from utils.myf import myf
from io import BytesIO
from xlrd import open_workbook
from urllib.parse import quote
import xlsxwriter
import datetime
import mimetypes
from utils.mydb import db1
from utils.xtree import xtree
from utils.getdata import getdata
from utils.myes import Es




bp_company = Blueprint("bp_company",__name__)

@bp_company.route("/test1")
def test():
    str = "请求ip:"+request.remote_addr+"请求url:"+request.url+"错误信息:"+'/n'
    # current_app.logger.info(str)
    # current_app.logger.warning(str)
    current_app.logger.error(str)
    return "ok"



@bp_company.route('/upload',methods=["POST"])
def upload():
    # 获取文件
    file = request.files['file']

    newname = myf.rename(file.filename)

    # 上传文件
    file.save(os.path.join('./static/files/' + newname))
    # 打开文件读取
    name = './static/files/' + newname
    bk = open_workbook(name, encoding_override='utf-8')
    sh = bk.sheets()[0]
    nrows = sh.nrows
    ncols = sh.ncols
    for i in range(1, nrows):
        row_date = sh.row_values(i)
        print(row_date[0])
        print(row_date[1])
    return jsonify({"msg":"ok"})

@bp_company.route('/download',methods=["POST","GET"])
def download():
    # 实例化二进制数据
    out=BytesIO()
    #  创建一个Excel实例
    workbook=xlsxwriter.Workbook(out)
    #  添加一个sheet，add_worksheet()，默认"Sheet1"，可自定义命名，add_worksheet("资产列表")
    table=workbook.add_worksheet()
    #  table.write(行, 列, 字段名)
    #  行、列从 0 开始计数
    #  字段命名根据每个人使用情况而定，插入数据部分也是
    sql="select * from company limit 0,10"
    res=db1.find_all(sql)
    table.write(0,0,"编号")
    table.write(0,1,"名称")
    num=1
    for i in res:
        table.write(num,0,i['id'])
        table.write(num,1,i['name'])
        num += 1
        workbook.close()
    filename=quote("{}.xls".format(datetime.datetime.now().strftime("%Y%m%d%H%M%S")))
    file=make_response(out.getvalue())
    out.close()
    mime_type=mimetypes.guess_type('%s.xls'%filename)[0]
    file.headers['Content-Type']=mime_type
    file.headers["Cache-Control"]="no-cache"
    file.headers['Content-Disposition']="attachment; filename*=utf-8''{}".format(filename)
    return file

@bp_company.route("/group")
def group():
    sql ="select * from company"
    res = db1.find_all(sql)
    data = xtree(res)
    return jsonify({"code":200,"list":data})

@bp_company.route("/addcompany",methods=["POST"])
def addcompany():
    data = getdata()
    sql = "insert into company(name,pid) values('%s',%d)"%(data["name"],int(data["pid"]))
    db1.update(sql)
    return jsonify({"code":200,"msg":"ok"})

@bp_company.route("/userslist")
def userslist():
    sql ="select * from users"
    res = db1.find_all(sql)

    return jsonify({"code":200,"list":res})

@bp_company.route("/deptesinsert")
def deptesinsert():
    es=Es("testes","test-type")
    #插入数据
    sql="select * from users limit 0,10"
    res=db1.find_all(sql)
    for i in res:
        es.insert(i)
    return "ok"
@bp_company.route("/getdatas")
def getdatas():
    test = request.args.get("test")
    es=Es("testes","test-type")
    query={"match":{"name":test}}
    #插入数据
    res = es.find(query)
    lst = []
    for i in res["hits"]["hits"]:
        lst.append(i["_source"])
    print(lst)
    return jsonify({"code":200,"list":lst})

